Troubleshoot PivotTable lists

Users who can view but not interact with your PivotTable list might not have the Microsoft Office Web Components installed or the appropriate Microsoft Office XP license. Be sure the components are installed and that the user has a valid license.

The field might already be displayed   You cannot display the same field in more than one area.

The source data might limit how you can use fields   Source data from On-Line Analytical Processing (OLAP) databases provides sets of fields for different levels of detail. Within a set of fields, you can hide levels of detail, but you cannot separate out fields in the set for display in different areas.

If the PivotTable list takes a long time to calculate, you can make several changes to the layout at one time and then retrieve the data when you are finished making changes.

1. When the Office logo cube is spinning, press ESC to cancel the calculation. The PivotTable list then displays only the names of the fields.

2. Change the layout, adding and moving as many fields as you want.

3. To retrieve and calculate the data for the new layout, click Refresh on the toolbar in the PivotTable list.

When you click a control on the Web page other than a Microsoft Office Web Component, or when you minimize and then restore the design window, the Commands and Options dialog box and the field list might be hidden.

To redisplay the dialog box or field list, click the PivotTable list so that it is reactivated.

Some PivotTable lists can't be refreshed   PivotTable lists that you save or publish from Microsoft Excel worksheet data, Excel Web queries, and Excel text queries can't be refreshed from the original source data in other design programs or in the browser. In these cases, you must refresh the original Excel workbook and republish the data.

The source database might be unavailable   You might not have a network connection, or you might need access privileges or a password to connect to the server. In the browser, users who want to display and work with the PivotTable list will need the same access and permissions for the source database as you have in the design program. Contact the network or database administrator for assistance.

Microsoft Office might not support the source database   If the source data is from a database that's not supported by Microsoft Office, you might need to install Office-compatible support software from the database vendor. Users need to install the same support software to refresh the PivotTable list in the browser.

The source database might have changed   Occasionally, changes to the way a database is organized on the server might prevent you from refreshing a PivotTable list. For example, if the source data is from an On-Line Analytical Processing (OLAP) database and the database is rebuilt with changes to the available data, you might have to create a new PivotTable list. Contact the database administrator for more information.

The source database might be unavailable   If the PivotTable list is based on source data from a database on a network, the database server might be unavailable, or the network or your connection to the network might have become unavailable. Contact the server or network administrator to check whether the server and network are available.

You might not have the permissions you need   You might be able to connect to the database, but you might not have permissions to run the query for the PivotTable list. Contact the database administrator to find out about permissions.

Check the filtering in the PivotTable list   Occasionally, a PivotTable list might be filtered to display specific items, and changes to the source database might eliminate one of those items, causing the query to fail. If this is the case, turn off filtering to display all items, and then click Refresh on the toolbar in the PivotTable list to run the query.

You cannot filter based on part of the data in a cell, a feature which is available for forms and datasheets in Microsoft Access. If you need partial-cell filtering, work with the source data in Access instead of a PivotTable list. You might be able to view the data in a form or datasheet directly in Access, where partial-cell filtering is supported. After you have filtered the data the way you want it in Access, you can use data access page Design view to create a new PivotTable list or change the existing one to use the filtered source data. For more information, see Access Help.

The source database might be unavailable   You might not have a network connection, or you might need access privileges or a password to connect to the server. In the browser, users who want to display and work with the PivotTable list will need the same access and permissions for the source database as you do in the design program. Contact the network or database administrator for assistance.

Microsoft Office might not support the source database   If the source data is from a database that's not supported by Microsoft Office, you might need to install Office-compatible support software from the database vendor. Users need to install the same support software to work with the PivotTable list in the browser. For information about the software you need, contact the database vendor.

The connection might not be set up correctly   To connect to source data, you must first set up a connection, or data source, between the source database and your design program. Make sure the data source you set up in your design program is working and allows you to access the database. Check Help in your design program and in the data source driver or ODBC driver for setup information. When you add the data to the PivotTable list, make sure you include all necessary connection information.

Expand indicators might be turned off   If you don't see the boxes to the left of items of data in row and column fields, make sure the expand indicators are turned on.

Items might not be available   When an item of data is at the lowest level of detail available from the source data, the item does not have the box beside it, even when you turn on the expand indicators.

The source data might not include detail data   Some types of source data, such as source data from On-Line Analytical Processing (OLAP) databases, do not have detail records available for display.

You canceled calculation of the PivotTable list, or calculation stopped before it was complete. To display the data, click Refresh on the toolbar in the PivotTable list.

Tip

When you copy data in a PivotTable list to the Clipboard and paste it into Microsoft Excel or Microsoft Word, only visible rows and columns are copied. Any data that you have hidden or filtered out is not copied.

Before you copy data, turn off filtering to show all data in the PivotTable list, and display the detail data that you want copied.

To avoid having to show all the data first, you can export, rather than copy, the PivotTable list. When you export a copy of a PivotTable list to Excel, all of the data is automatically included, with the same filtering in effect. You have the same access to additional fields in the source data that you do in a PivotTable list, and you can change the filtering in Excel. From Excel, you can then copy data to Word or other programs, or you can publish the data again to a PivotTable list. For information about working with PivotTable reports in Excel and publishing them to a Web page, see Help in Excel.

When a number is too wide to fit in a cell, the PivotTable list displays number signs instead of the number. You can fix this in any of the following ways:

• Make the column wider so that the entire number fits.

• Make the font smaller if you don't want to widen the column.

• Change the number format to one that takes less space.

• The properties on the Protection tab of the Commands and Options dialog box have been set to prevent additions, edits, or deletions.

• The field you are trying to edit is a read-only field, and does not support edits.

• If the PivotTable list is on a data access page that supports transactions, the PivotTable list doesn't support additions, edits, or deletions.

Add total fields   For a chart to display data from a PivotTable list, you must add at least one total field to the PivotTable list.

Move fields to the row and column areas   The row and column fields in a PivotTable list provide the category and series data in your chart. If all of the fields are in the detail area of the PivotTable list, for example, the chart has no categories and series to plot. From the detail area or field list, move at least one field to the row area or the column area.

Asterisks (*) appear next to the caption of the total fields in the following cases:

• When you set the option to include all items in totals, asterisks appear next to the caption of the total fields to indicate that the totals include any hidden items in addition to the displayed items.

• In Excel PivotTable reports that are based on source data from On-Line Analytical Processing (OLAP) databases, the calculations for subtotal and grand total values always include the values for all items in the field, both displayed and hidden. Excel provides an option to turn off display of these asterisks. 

In the PivotTable list, regardless of whether it's based on source data from an OLAP database, if you want to omit data that is hidden by filtering from subtotals and grand totals, you can set an option to include only displayed data. This option also hides the asterisks.